-- @owner: Alice
-- @date: 2025-09-03
-- @testpoint: 确定性函数支持result_cache，语法测试，合理报错
@conn PrimaryDbAmode;
drop extension if exists gms_output;
drop function if exists func_immutable;
drop function if exists func_stable;
drop function if exists func_volatile;
drop function if exists func_default;
drop function if exists func_deterministic;
drop function if exists func_order;
drop function if exists func_nocache;
drop function if exists func_transform;
drop function if exists func_immutable_yc;
drop function if exists func_clob;
drop function if exists func_immutable;
drop function if exists func_increment_plsql;
create extension gms_output;
--case1
set enable_func_cache = 1;
select gms_output.enable();
set query_dop=1;

set datestyle='ISO,MDY';
--创建表
drop table if exists tt_cache;
create table tt_cache(
id int,
col_smallint smallint
);
insert into tt_cache values(
generate_series(1,20)%4,
generate_series(1,20)%3
);
select count(*) from tt_cache;
--create function
create or replace function func_immutable(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
end if;
return p+1;
end;
$$ language plpgsql immutable result_cache;
/
select func_immutable(12) from tt_cache;
select func_immutable(col_smallint) from tt_cache;
create or replace function func_stable(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
end if;
return p+1;
end;
$$ language plpgsql stable result_cache;
/
select func_stable(13) from tt_cache;
select func_stable(col_smallint) from tt_cache;
create or replace function func_volatile(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
end if;
return p+1;
end;
$$ language plpgsql volatile result_cache;
/
select func_volatile(13) from tt_cache;
select func_volatile(col_smallint) from tt_cache;
create or replace function func_default(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
end if;
return p+1;
end;
$$ language plpgsql result_cache;
/
select func_default(13) from tt_cache;
select func_default(col_smallint) from tt_cache;
create or replace function func_deterministic(p smallint) returns smallint immutable as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
end if;
return p+1;
end;
$$ language plpgsql result_cache;
/
select func_deterministic(13) from tt_cache;
select func_deterministic(col_smallint) from tt_cache;
create or replace function func_order(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
end if;
return p+1;
end;
$$ language plpgsql result_cache immutable;
/
select func_order(13) from tt_cache;
select func_order(col_smallint) from tt_cache;
create or replace function func_nocache(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
end if;
return p+1;
end;
$$ language plpgsql immutable;
/
select func_nocache(13) from tt_cache;
select func_nocache(col_smallint) from tt_cache;
SELECT p.proname,pe.result_cache FROM pg_proc p JOIN pg_proc_ext pe ON p.oid = pe.proc_oid where p.proname in ('func_immutable','func_stable','func_volatile','func_default','func_order','func_nocache') order by p.proname;
--case2
--函数从nocache变成cache
create or replace function func_transform(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
end if;
return p+1;
end;
$$ language plpgsql immutable;
/
select func_transform(13) from tt_cache;
select func_transform(col_smallint) from tt_cache;
SELECT p.proname,pe.result_cache FROM pg_proc p JOIN pg_proc_ext pe ON p.oid = pe.proc_oid where p.proname in ('func_transform') order by p.proname;
alter function func_transform(smallint) result_cache;
SELECT p.proname,pe.result_cache FROM pg_proc p JOIN pg_proc_ext pe ON p.oid = pe.proc_oid where p.proname in ('func_transform') order by p.proname;
select func_transform(13) from tt_cache;
select func_transform(col_smallint) from tt_cache;
--异常场景
--case3 result_cache出现多次，bug1052535
create or replace function func_immutable_yc(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
end if;
return p+1;
end;
$$ language plpgsql result_cache immutable result_cache;
/
select func_immutable_yc(13) from tt_cache;
select func_immutable_yc(col_smallint) from tt_cache;
--case4 不支持的数据类型
create or replace function func_clob(p smallint) returns clob as $$
declare
v_clob CLOB:='aaa';
begin
raise notice 'NOTICE: excute function...';
return v_clob;
end;
$$ language plpgsql result_cache immutable;
/
SELECT p.proname,pe.result_cache FROM pg_proc p JOIN pg_proc_ext pe ON p.oid = pe.proc_oid where p.proname in ('func_clob') order by p.proname;
select func_clob(13) from tt_cache;
select func_clob(col_smallint) from tt_cache;
--case5  in out参数 ，bug1052535
create or replace function func_immutable(p in out smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
end if;
return p+1;
end;
$$ language plpgsql result_cache immutable;
/
select func_immutable(13) from tt_cache;
select func_immutable(col_smallint) from tt_cache;
--case6
--drop function if exists func_increment_plsql(integer);
--create or replace function func_increment_plsql(i integer) RETURNS integer result_cache AS $$
--BEGIN
--raise notice 'NOTICE: excute function...';
--RETURN i + 1;
--END;
--$$ LANGUAGE plpgsql result_cache;
/
--select func_increment_plsql(13) from tt_cache;
--select func_increment_plsql(col_smallint) from tt_cache;
--teardown
drop function if exists func_immutable;
drop function if exists func_stable;
drop function if exists func_volatile;
drop function if exists func_default;
drop function if exists func_deterministic;
drop function if exists func_order;
drop function if exists func_nocache;
drop function if exists func_transform;
drop function if exists func_immutable_yc;
drop function if exists func_clob;
drop function if exists func_immutable;
drop function if exists func_increment_plsql;
